package com.physique.tea.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.physique.tea.Pojo.Order;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
    // ========== 增 ==========
    @Insert("INSERT INTO ORDERS (ORDERID, ADDRESS, ORDERDATE, USERNAME, RECIPE, PHONE, ORDERPRICES, STATE) " +
            "VALUES (#{ORDERID}, #{ADDRESS}, #{ORDERDATE}, #{USERNAME}, #{RECIPE}, #{PHONE}, #{ORDERPRICES}, #{STATE})")
    @Options(useGeneratedKeys = true, keyProperty = "ORDERID")
    int insertOrder(Order order);

    // ========== 删 ==========
    @Delete("DELETE FROM ORDERS WHERE ORDERID = #{ORDERID}")
    int deleteById(@Param("ORDERID") Integer ORDERID);

    @Delete("DELETE FROM ORDERS WHERE USERNAME = #{USERNAME}")
    int deleteByUsername(@Param("USERNAME") String USERNAME);

    // ========== 改 ==========
    @Update("UPDATE ORDERS SET " +
            "ADDRESS = #{ADDRESS}, " +
            "ORDERDATE = #{ORDERDATE}, " +
            "USERNAME = #{USERNAME}, " +
            "RECIPE = #{RECIPE}, " +
            "PHONE = #{PHONE}, " +
            "ORDERPRICES = #{ORDERPRICES}, " +
            "STATE = #{STATE} " +
            "WHERE ORDERID = #{ORDERID}")
    int updateOrder(Order order);

    @Update("UPDATE ORDERS SET STATE = #{STATE} WHERE ORDERID = #{ORDERID}")
    int updateOrderState(@Param("ORDERID") Integer ORDERID, @Param("STATE") Integer STATE);

    @Update("UPDATE ORDERS SET PHONE = #{PHONE} WHERE ORDERID = #{ORDERID}")
    int updateOrderPhone(@Param("ORDERID") Integer ORDERID, @Param("PHONE") String PHONE);

    // ========== 查 ==========
    @Select("SELECT ORDERID, ADDRESS, ORDERDATE, USERNAME, RECIPE, PHONE, ORDERPRICES, STATE " +
            "FROM ORDERS WHERE ORDERID = #{ORDERID}")
    Order selectById(@Param("ORDERID") Integer ORDERID);

    @Select("SELECT ORDERID, ADDRESS, ORDERDATE, USERNAME, RECIPE, PHONE, ORDERPRICES, STATE " +
            "FROM ORDERS WHERE USERNAME = #{USERNAME} ORDER BY ORDERDATE DESC")
    List<Order> selectByUsername(@Param("USERNAME") String USERNAME);

    @Select("SELECT ORDERID, ADDRESS, ORDERDATE, USERNAME, RECIPE, PHONE, ORDERPRICES, STATE " +
            "FROM ORDERS WHERE PHONE = #{PHONE} ORDER BY ORDERDATE DESC")
    List<Order> selectByPhone(@Param("PHONE") String PHONE);

    @Select("SELECT ORDERID, ADDRESS, ORDERDATE, USERNAME, RECIPE, PHONE, ORDERPRICES, STATE " +
            "FROM ORDERS WHERE STATE = #{STATE} ORDER BY ORDERDATE DESC")
    List<Order> selectByState(@Param("STATE") Integer STATE);

    @Select("SELECT ORDERID, ADDRESS, ORDERDATE, USERNAME, RECIPE, PHONE, ORDERPRICES, STATE " +
            "FROM ORDERS WHERE ORDERDATE BETWEEN #{START_DATE} AND #{END_DATE} ORDER BY ORDERDATE DESC")
    List<Order> selectByDateRange(@Param("START_DATE") Date START_DATE, @Param("END_DATE") Date END_DATE);

    @Select("SELECT ORDERID, ADDRESS, ORDERDATE, USERNAME, RECIPE, PHONE, ORDERPRICES, STATE " +
            "FROM ORDERS WHERE RECIPE LIKE '%' || #{RECIPE_KEYWORD} || '%' ORDER BY ORDERDATE DESC")
    List<Order> searchByRecipe(@Param("RECIPE_KEYWORD") String RECIPE_KEYWORD);

    // ========== 统计查询 ==========
    @Select("SELECT STATE, COUNT(*) AS COUNT FROM ORDERS GROUP BY STATE")
    List<Map<String, Object>> countByState();

    @Select("SELECT USERNAME, SUM(ORDERPRICES) AS TOTAL_AMOUNT " +
            "FROM ORDERS GROUP BY USERNAME ORDER BY TOTAL_AMOUNT DESC")
    List<Map<String, Object>> sumAmountByUser();

    @Select("SELECT TO_CHAR(ORDERDATE, 'YYYY-MM') AS MONTH, SUM(ORDERPRICES) AS MONTHLY_TOTAL " +
            "FROM ORDERS GROUP BY TO_CHAR(ORDERDATE, 'YYYY-MM') ORDER BY MONTH")
    List<Map<String, Object>> sumAmountByMonth();
}